// Copyright (C) 2004-2007 MySQL AB
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License version 2 as published by
// the Free Software Foundation
//
// There are special exceptions to the terms and conditions of the GPL 
// as it is applied to this software. View the full text of the 
// exception in file EXCEPTIONS in the directory of this software 
// distribution.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Data;
using System.ComponentModel;
using System.IO;
using System.Collections;
using System.Text;
using MySql.Data.Common;

namespace MySql.Data.MySqlClient
{
	/// <include file='docs/mysqlcommand.xml' path='docs/ClassSummary/*'/>
#if WINDOWS
	[System.Drawing.ToolboxBitmap(typeof(MySqlCommand), "MySqlClient.resources.command.bmp")]
#endif
	[System.ComponentModel.DesignerCategory("Code")]
	public sealed class MySqlCommand : Component, IDbCommand, ICloneable
	{
		MySqlConnection connection;
		MySqlTransaction curTransaction;
		string cmdText;
		CommandType cmdType;
		long updateCount;
		UpdateRowSource updatedRowSource;
		MySqlParameterCollection parameters;
		private ArrayList sqlBuffers;
		private PreparedStatement preparedStatement;
		private ArrayList parameterMap;
		private StoredProcedure storedProcedure;
		private CommandResult lastResult;

		/// <include file='docs/mysqlcommand.xml' path='docs/ctor1/*'/>
		public MySqlCommand()
		{
			cmdType = CommandType.Text;
			parameterMap = new ArrayList();
			parameters = new MySqlParameterCollection();
			updatedRowSource = UpdateRowSource.Both;
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ctor2/*'/>
		public MySqlCommand(string cmdText)
			: this()
		{
			CommandText = cmdText;
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ctor3/*'/>
		public MySqlCommand(string cmdText, MySqlConnection connection)
			: this(cmdText)
		{
			Connection = connection;
			if (connection != null)
				parameters.ParameterMarker = connection.ParameterMarker;
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ctor4/*'/>
		public MySqlCommand(string cmdText, MySqlConnection connection,
			MySqlTransaction transaction)
			: this(cmdText, connection)
		{
			curTransaction = transaction;
		}

		#region Properties

		/// <include file='docs/mysqlcommand.xml' path='docs/CommandText/*'/>
		[Category("Data")]
		[Description("Command text to execute")]
#if WINDOWS
		[Editor("MySql.Data.Common.Design.SqlCommandTextEditor,MySqlClient.Design", typeof(System.Drawing.Design.UITypeEditor))]
#endif
		public string CommandText
		{
			get { return cmdText; }
			set { cmdText = value; this.preparedStatement = null; }
		}

		internal int UpdateCount
		{
			get { return (int)updateCount; }
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/CommandTimeout/*'/>
		[Category("Misc")]
		[Description("Time to wait for command to execute")]
		public int CommandTimeout
		{
			// TODO: support this
			get { return 0; }
			set { if (value != 0) throw new NotSupportedException(); }
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/CommandType/*'/>
		[Category("Data")]
		public CommandType CommandType
		{
			get { return cmdType; }
			set { cmdType = value; }
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/IsPrepared/*'/>
		[Browsable(false)]
		public bool IsPrepared
		{
			get { return preparedStatement != null; }
		}

		IDbConnection IDbCommand.Connection
		{
			get { return connection; }
			set { Connection = (MySqlConnection)value; }
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/Connection/*'/>
		[Category("Behavior")]
		[Description("Connection used by the command")]
		public MySqlConnection Connection
		{
			get { return connection; }
			set
			{
				/*
				* The connection is associated with the transaction
				* so set the transaction object to return a null reference if the connection 
				* is reset.
				*/
				if (connection != value)
					this.Transaction = null;

				connection = (MySqlConnection)value;
				if (connection != null)
					parameters.ParameterMarker = connection.ParameterMarker;
			}
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/Parameters/*'/>
		[Category("Data")]
		[Description("The parameters collection")]
		[DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
		public MySqlParameterCollection Parameters
		{
			get { return parameters; }
		}

		IDataParameterCollection IDbCommand.Parameters
		{
			get { return parameters; }
		}

		IDbTransaction IDbCommand.Transaction
		{
			get { return Transaction; }
			set { Transaction = (MySqlTransaction)value; }
		}


		/// <include file='docs/mysqlcommand.xml' path='docs/Transaction/*'/>
		[Browsable(false)]
		public MySqlTransaction Transaction
		{
			get { return curTransaction; }
			set { curTransaction = (MySqlTransaction)value; }
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/UpdatedRowSource/*'/>
		[Category("Behavior")]
		public UpdateRowSource UpdatedRowSource
		{
			get
			{
				return updatedRowSource;
			}
			set
			{
				updatedRowSource = value;
			}
		}
		#endregion

		#region Methods

		/// <summary>
		/// Attempts to cancel the execution of a MySqlCommand.  This operation is not supported.
		/// </summary>
		/// <remarks>
		/// Cancelling an executing command is currently not supported on any version of MySQL.
		/// </remarks>
		/// <exception cref="NotSupportedException">This operation is not supported.</exception>
		public void Cancel()
		{
			throw new NotSupportedException();
		}

		/// <summary>
		/// Creates a new instance of a <see cref="MySqlParameter"/> object.
		/// </summary>
		/// <remarks>
		/// This method is a strongly-typed version of <see cref="IDbCommand.CreateParameter"/>.
		/// </remarks>
		/// <returns>A <see cref="MySqlParameter"/> object.</returns>
		/// 
		public MySqlParameter CreateParameter()
		{
			return new MySqlParameter();
		}

		IDbDataParameter IDbCommand.CreateParameter()
		{
			return this.CreateParameter();
		}

		/// <summary>
		/// Executes all remaining command buffers
		/// </summary>
		internal void Consume()
		{
			CommandResult result = GetNextResultSet(null);
			while (result != null)
			{
				result.Consume();
				result = GetNextResultSet(null);
			}

			// if we were executing a stored procedure and we are out of sql buffers to execute, 
			// then we need to perform some additional work to get our inout and out parameters
			if (storedProcedure != null)
				storedProcedure.UpdateParameters(Parameters);
		}

		/// <summary>
		/// Executes command buffers until we hit the next resultset
		/// </summary>
		/// <returns>CommandResult containing the next resultset when hit
		/// or null if no more resultsets were found</returns>
		internal CommandResult GetNextResultSet(MySqlDataReader reader)
		{
			// if  we are supposed to return only a single resultset and our reader
			// is calling us back again, then return null
			if (reader != null &&
				(reader.Behavior & CommandBehavior.SingleResult) != 0 &&
				lastResult != null) return null;

			// if the last result we returned has more results
			if (lastResult != null && lastResult.ReadNextResult(false))
				return lastResult;
			lastResult = null;

			CommandResult result = null;

			// if we haven't prepared a statement and don't have any sql buffers
			// to execute, we are done
			if (preparedStatement == null)
			{
				if (sqlBuffers == null || sqlBuffers.Count == 0)
					return null;
			}
			else if (preparedStatement.ExecutionCount > 0)
				return null;


			// if we have a prepared statement, we execute it instead
			if (preparedStatement != null)
			{
				result = preparedStatement.Execute(parameters);

				if (!result.IsResultSet)
				{
					if (updateCount == -1) updateCount = 0;
					updateCount += (long)result.AffectedRows;
				}
			}
			else while (sqlBuffers.Count > 0)
				{
					MemoryStream sqlStream = (MemoryStream)sqlBuffers[0];

					using (sqlStream)
					{
						result = connection.driver.SendQuery(sqlStream.GetBuffer(), (int)sqlStream.Length, false);
						sqlBuffers.RemoveAt(0);
					}

					if (result.AffectedRows != -1)
					{
						if (updateCount == -1) updateCount = 0;
						updateCount += (long)result.AffectedRows;
					}

					// if this is a resultset, then we break out of our execution loop
					if (result.IsResultSet)
						break;
				}

			if (result.IsResultSet)
			{
				lastResult = result;
				return result;
			}
			return null;
		}

		/// <summary>
		/// Check the connection to make sure
		///		- it is open
		///		- it is not currently being used by a reader
		///		- and we have the right version of MySQL for the requested command type
		/// </summary>
		private void CheckState()
		{
			// There must be a valid and open connection.
			if (connection == null || connection.State != ConnectionState.Open)
				throw new InvalidOperationException(Resources.ConnectionMustBeOpen);

			// Data readers have to be closed first
			if (connection.Reader != null)
				throw new MySqlException(Resources.DataReaderOpen);

			if (CommandType == CommandType.StoredProcedure && !connection.driver.Version.isAtLeast(5, 0, 0))
				throw new MySqlException(Resources.SPNotSupported);
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ExecuteNonQuery/*'/>
		public int ExecuteNonQuery()
		{
            lastResult = null;
			CheckState();

			if (cmdText == null ||
				 cmdText.Trim().Length == 0)
				throw new InvalidOperationException(Resources.CommandTextNotInitialized);

			updateCount = 0;

			if (preparedStatement == null)
				sqlBuffers = PrepareSqlBuffers(CommandText);
			else
				preparedStatement.ExecutionCount = 0;

            try
            {
                Consume();
            }
            catch (MySqlException mex)
            {
                if (mex.IsFatal) connection.Terminate();
                throw;
            }
            catch (Exception)
            {
                connection.Terminate();
                throw;
            }

			return (int)updateCount;
		}

		IDataReader IDbCommand.ExecuteReader()
		{
			return ExecuteReader();
		}

		IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
		{
			return ExecuteReader(behavior);
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ExecuteReader/*'/>
		public MySqlDataReader ExecuteReader()
		{
			return ExecuteReader(CommandBehavior.Default);
		}


		/// <include file='docs/mysqlcommand.xml' path='docs/ExecuteReader1/*'/>
		public MySqlDataReader ExecuteReader(CommandBehavior behavior)
		{
            lastResult = null;
            CheckState();

			if (cmdText == null ||
				 cmdText.Trim().Length == 0)
				throw new InvalidOperationException(Resources.CommandTextNotInitialized);

			string sql = TrimSemicolons(cmdText);

			updateCount = -1;
			MySqlDataReader reader = new MySqlDataReader(this, behavior);

			// if we don't have a prepared statement, then prepare our sql for execution
			if (preparedStatement == null)
				sqlBuffers = PrepareSqlBuffers(sql);
			else
				preparedStatement.ExecutionCount = 0;

           HandleCommandBehaviors(behavior);

			reader.NextResult();
			connection.Reader = reader;
			return reader;
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/ExecuteScalar/*'/>
		public object ExecuteScalar()
		{
            lastResult = null;
            // ExecuteReader will check out state
			if (cmdText == null ||
				 cmdText.Trim().Length == 0)
				throw new InvalidOperationException(Resources.CommandTextNotInitialized);

			updateCount = -1;

			object val = null;
			MySqlDataReader reader = null;
			try
			{
				reader = ExecuteReader();
				if (reader.Read())
					val = reader.GetValue(0);
			}
			catch (Exception)
			{
				throw;
			}
			finally
			{
				if (reader != null)
					reader.Close();
			}

			return val;
		}

		/// <include file='docs/mysqlcommand.xml' path='docs/Prepare/*'/>
		public void Prepare()
		{
			if (connection == null)
				throw new InvalidOperationException(Resources.ConnectionNotSet);
			if (connection.State != ConnectionState.Open)
				throw new InvalidOperationException(Resources.ConnectionNotOpen);
			if (!connection.driver.Version.isAtLeast(4, 1, 0) ||
				 connection.Settings.IgnorePrepare)
				return;

			// strip out names from parameter markers
			// if the length of the command text is zero, then just return
			string psSQL = CommandText;
			if (psSQL == null ||
				 psSQL.Trim().Length == 0)
				return;

			if (CommandType == CommandType.StoredProcedure)
			{
				if (storedProcedure == null)
					storedProcedure = new StoredProcedure(connection);
				psSQL = storedProcedure.Prepare(this);
			}
			psSQL = PrepareCommandText(psSQL);

			// ask our connection to send the prepare command
			preparedStatement = connection.driver.Prepare(psSQL, (string[])parameterMap.ToArray(typeof(string)));
		}
		#endregion


		#region Private Methods

		private void HandleCommandBehaviors(CommandBehavior behavior)
		{
			MySqlCommand cmd = new MySqlCommand("", connection);
			int selectLimit = -1;

			// if we are asked to provide only schema or single row resultsets, then
			// set SQL_SELECT_LIMIT to optimize the process
			if (0 != (behavior & CommandBehavior.SchemaOnly))
				selectLimit = 0;
			else if (0 != (behavior & CommandBehavior.SingleRow))
				selectLimit = 1;
			else if (connection.driver.selectLimit != -1)
				selectLimit = -1;
			else
				return;

			try
			{
				cmd.CommandText = String.Format("SET SQL_SELECT_LIMIT={0}", selectLimit);
				cmd.ExecuteNonQuery();
				connection.driver.selectLimit = selectLimit;
			}
			catch (Exception)
			{
				throw;
			}
		}

		private string TrimSemicolons(string sql)
		{
			System.Text.StringBuilder sb = new System.Text.StringBuilder(sql);
			int start = 0;
			while (sb[start] == ';')
				start++;

			int end = sb.Length - 1;
			while (sb[end] == ';')
				end--;
			return sb.ToString(start, end - start + 1);
		}

		/// <summary>
		/// We use a separate method here because we want to support using parameter
		/// names with and without a leading marker but we don't want the indexing
		/// methods of MySqlParameterCollection to support that.
		/// </summary>
		/// <param name="name"></param>
		/// <returns></returns>
		private MySqlParameter GetParameter(MySqlParameterCollection parameters, string name)
		{
			string parmName = name;
			int index = parameters.IndexOf(name);
			if (index == -1)
			{
				name = name.Substring(1);
				index = Parameters.IndexOf(name);
				if (index == -1)
					return null;
			}
			return parameters[index];
		}

		/// <summary>
		/// Serializes the given parameter to the given memory stream
		/// </summary>
		/// <param name="writer">PacketWriter to stream parameter data to</param>
		/// <param name="parmName">Name of the parameter to serialize</param>
		/// <remarks>
		/// <para>This method is called by PrepareSqlBuffers to convert the given
		/// parameter to bytes and write those bytes to the given memory stream.
		/// </para>
		/// </remarks>
		/// <returns>True if the parameter was successfully serialized, false otherwise.</returns>
		private bool SerializeParameter(PacketWriter writer, string parmName)
		{
			MySqlParameter parameter = GetParameter(parameters, parmName);
			if (parameter == null)
			{
				// if we are using old syntax, we can't throw exceptions for parameters
				// not defined.
				if (Connection.Settings.UseOldSyntax)
					return false;
				throw new MySqlException(
					String.Format(Resources.ParameterMustBeDefined));
			}

			parameter.Serialize(writer, false);
			return true;
		}


		/// <summary>
		/// Prepares the necessary byte buffers from the given CommandText
		/// </summary>
		/// <returns>Array of byte buffers, one for each SQL command</returns>
		/// <remarks>
		/// Converts the CommandText into an array of tokens 
		/// using TokenizeSql and then into one or more byte buffers that can be
		/// sent to the server.  If the server supports batching (and we  have enabled it),
		/// then all commands result in a single byte array, otherwise a single buffer
		/// is created for each SQL command (as separated by ';').
		/// The SQL text is converted to bytes using the active encoding for the server.
		/// </remarks>
		private ArrayList PrepareSqlBuffers(string sql)
		{
			ArrayList buffers = new ArrayList();
			PacketWriter writer = new PacketWriter();
			writer.Encoding = connection.Encoding;
			writer.Version = connection.driver.Version;

			// if we are executing as a stored procedure, then we need to add the call
			// keyword.
			if (CommandType == CommandType.StoredProcedure)
			{
				if (storedProcedure == null)
					storedProcedure = new StoredProcedure(connection);
				sql = storedProcedure.Prepare(this);
			}

			// tokenize the SQL
			sql = sql.TrimStart(';').TrimEnd(';');
			ArrayList tokens = TokenizeSql(sql);

			foreach (string token in tokens)
			{
				if (token.Trim().Length == 0) continue;
				if (token == ";" && !connection.driver.SupportsBatch)
				{
					MemoryStream ms = (MemoryStream)writer.Stream;
					if (ms.Length > 0)
						buffers.Add(ms);

					writer = new PacketWriter();
					writer.Encoding = connection.Encoding;
					writer.Version = connection.driver.Version;
					continue;
				}
				else if (token[0] == parameters.ParameterMarker)
				{
					if (SerializeParameter(writer, token)) continue;
				}

				// our fall through case is to write the token to the byte stream
				writer.WriteStringNoNull(token);
			}

			// capture any buffer that is left over
			MemoryStream mStream = (MemoryStream)writer.Stream;
			if (mStream.Length > 0)
				buffers.Add(mStream);

			return buffers;
		}

		/// <summary>
		/// Prepares CommandText for use with the Prepare method
		/// </summary>
		/// <returns>Command text stripped of all paramter names</returns>
		/// <remarks>
		/// Takes the output of TokenizeSql and creates a single string of SQL
		/// that only contains '?' markers for each parameter.  It also creates
		/// the parameterMap array list that includes all the paramter names in the
		/// order they appeared in the SQL
		/// </remarks>
		private string PrepareCommandText(string text)
		{
			StringBuilder newSQL = new StringBuilder();

			// tokenize the sql first
			ArrayList tokens = TokenizeSql(text);
			parameterMap.Clear();

			foreach (string token in tokens)
			{
				if (token[0] != parameters.ParameterMarker)
					newSQL.Append(token);
				else
				{
					parameterMap.Add(token);
					newSQL.Append(parameters.ParameterMarker);
				}
			}

			return newSQL.ToString();
		}

		/// <summary>
		/// Breaks the given SQL up into 'tokens' that are easier to output
		/// into another form (bytes, preparedText, etc).
		/// </summary>
		/// <param name="sql">SQL to be tokenized</param>
		/// <returns>Array of tokens</returns>
		/// <remarks>The SQL is tokenized at parameter markers ('?') and at 
		/// (';') sql end markers if the server doesn't support batching.
		/// </remarks>
		private ArrayList TokenizeSql(string sql)
		{
			char delim = Char.MinValue;
			StringBuilder sqlPart = new StringBuilder();
			bool escaped = false;
			ArrayList tokens = new ArrayList();

			for (int i = 0; i < sql.Length; i++)
			{
				char c = sql[i];
				if (escaped)
					escaped = !escaped;
				else if (c == delim)
					delim = Char.MinValue;
				else if (c == ';' && !escaped && delim == Char.MinValue && !connection.driver.SupportsBatch)
				{
					tokens.Add(sqlPart.ToString());
					tokens.Add(";");
					sqlPart.Remove(0, sqlPart.Length);
					continue;
				}
				else if ((c == '\'' || c == '\"' || c == '`') & !escaped & delim == Char.MinValue)
					delim = c;
				else if (c == '\\')
					escaped = !escaped;
				else if (c == parameters.ParameterMarker && delim == Char.MinValue && !escaped)
				{
					tokens.Add(sqlPart.ToString());
					sqlPart.Remove(0, sqlPart.Length);
				}
				else if (sqlPart.Length > 0 && sqlPart[0] == parameters.ParameterMarker &&
					!Char.IsLetterOrDigit(c) && c != '_' && c != '.' && c != '$'
					&& c != '@')
				{
					tokens.Add(sqlPart.ToString());
					sqlPart.Remove(0, sqlPart.Length);
				}

				sqlPart.Append(c);
			}
			tokens.Add(sqlPart.ToString());
			return tokens;
		}
		#endregion

		#region ICloneable
		/// <summary>
		/// Creates a clone of this MySqlCommand object.  CommandText, Connection, and Transaction properties
		/// are included as well as the entire parameter list.
		/// </summary>
		/// <returns>The cloned MySqlCommand object</returns>
		object ICloneable.Clone()
		{
			MySqlCommand clone = new MySqlCommand(cmdText, connection, curTransaction);
			foreach (MySqlParameter p in parameters)
			{
				clone.Parameters.Add((p as ICloneable).Clone());
			}
			return clone;
		}
		#endregion
	}
}
